﻿CREATE VIEW GP.vw_FamiglieColoriXPolveri
AS
	with cteDati(idPolvere, applicazione, idfamiglia) AS 
	(
		select distinct cc.idPolvere, cc.applicazione, af.idfamiglia from gp.composizionecolori as cc
		inner join gp.anagcolori as ac on cc.idcolore = ac.idcolore
		inner join gp.anagfamiglie as af on ac.idfamiglia = af.idfamiglia
	)
	select 
	distinct ap.idpolvere
	,Stuff(( select distinct ',' + CAST(Sub.idfamiglia as varchar) AS [text()] from cteDati as Sub where Sub.idPolvere = cteDati.idPolvere for xml path('')), 1, 1, '') as 'Famiglia'
	,Stuff(( select distinct ',' + Sub.Applicazione AS [text()] from cteDati as Sub where Sub.idPolvere = cteDati.idPolvere for xml path('')), 1, 1, '') as 'Applicazione'
	from gp.anagpolveri as ap
	inner join cteDati on cteDati.idPolvere = ap.idPolvere  